package com.loongtech.bi.manager.player;

import java.util.Date;
import java.util.List;

import com.loongtech.bi.entity.player.EntityDaPlayTimeTag;
import org.springframework.stereotype.Service;

import com.loongtech.bi.entity.player.EntityDaPlayTime;
import com.loongtech.core.jpa.manager.ManagerQueryCacheBase;

@Service
public class DaPlayTimeManager extends ManagerQueryCacheBase<EntityDaPlayTime> {
	private static final long serialVersionUID = 1L;
	
	/**
	 * 根据开始结束时间，求平均在线时长
	 * @param dateStart
	 * @param dateEnd
	 * @param type
	 * @return
	 */
	public List<EntityDaPlayTime> getDaPlayTimeByDate(Date dateStart, Date dateEnd) {
		return findByQuery("from EntityDaPlayTime where 1=1 and day>=:dayStart and day<=:dayEnd order by day ", "dayStart", dateStart, "dayEnd", dateEnd);
	}

    /**
     * 根据开始结束时间，求平均在线时长
     * @param dateStart
     * @param dateEnd
     * @param type
     * @return
     */
    public List<EntityDaPlayTime> getDaPlayTimeByDate2(Date dateStart, Date dateEnd) {
        return findByQuery("from EntityDaPlayTime where 1=1 and day>=:dayStart and day<=:dayEnd and serverid='0' order by day ", "dayStart", dateStart, "dayEnd", dateEnd);
    }


    /**
     * 根据开始结束时间服务器id，求平均在线时长
     * @param dateStart
     * @param dateEnd
     * @return
     */
    public List<EntityDaPlayTimeTag> getDaPlayTimeByDateByTag(String serverid, Date dateStart, Date dateEnd) {
        return findByQuery("from EntityDaPlayTimeTag where serverid=? and day>=? and day<=? order by day ", serverid, dateStart, dateEnd);
    }

	/**
	 * 根据开始结束时间，求平均在线时长
	 * @param dateStart
	 * @param dateEnd
	 * @param type
	 * @return
	 */
	public List<Object> getDaPlayTimeByPlatform(Date dateStart, Date dateEnd) {
		return findByQuery("select sum(da_playtime) / 60 from EntityDaPlayTime  where 1=1 and day>=:dayStart and day<=:dayEnd and platformid != '0' and sumplaytime is not null and useridnum is not null", "dayStart", dateStart, "dayEnd", dateEnd);
	}
	
	/**
	 * 根据开始结束时间查询某个维度的数值
	 * @param dateStart
	 * @param dateEnd
	 * @return
	 */
	public List<Object[]> getDaPlayTimeByAllKind(Date dateStart, Date dateEnd,String queryPlatform, String queryChannel, String queryServer) {
		return findByQuery("select date_format(day,'%Y-%m-%d'),da_playtime / 60 from EntityDaPlayTime where day>=:dayStart and day<=:dayEnd and platformid = '"+queryPlatform+"' and channelid = '"+queryChannel+"' and serverid = '"+queryServer+"' and da_playtime is not null order by day desc", "dayStart", dateStart, "dayEnd", dateEnd);
	}
	
	/**
	 * 根据platformid 查询
	 * @param dateStart
	 * @param dateEnd
	 * @return
	 */
	public List<Object[]> getDaPlayTimeGroupByPlatform(Date dateStart, Date dateEnd) {
		return findByQuery("select platformid,sum(sumplaytime) / sum(useridnum) / 60 from EntityDaPlayTime where day>=:dayStart and day<=:dayEnd and platformid != '0' and sumplaytime is not null and useridnum is not null group by platformid", "dayStart", dateStart, "dayEnd", dateEnd);
	}
	
	/**
	 * 根据channelid 查询
	 * @param dateStart
	 * @param dateEnd
	 * @return
	 */
	public List<Object[]> getDaPlayTimeGroupByChannel(Date dateStart, Date dateEnd) {
		return findByQuery("select channelid,sum(sumplaytime) / sum(useridnum) / 60 from EntityDaPlayTime where day>=:dayStart and day<=:dayEnd and channelid != '0' and sumplaytime is not null and useridnum is not null group by channelid", "dayStart", dateStart, "dayEnd", dateEnd);
	}
	
	/**
	 * 根据serverid 查询
	 * @param dateStart
	 * @param dateEnd
	 * @return
	 */
	public List<Object[]> getDaPlayTimeGroupByServer(Date dateStart, Date dateEnd) {
		return findByQuery("select serverid,sum(sumplaytime) / sum(useridnum) / 60 from EntityDaPlayTime where day>=:dayStart and day<=:dayEnd and serverid != '0' and sumplaytime is not null and useridnum is not null group by serverid", "dayStart", dateStart, "dayEnd", dateEnd);
	}
}
